import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;

import java.io.*;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 将JSON格式的面试题转换为SQL语句的工具类
 */
public class InterviewQuestionToSQL {

    // JSON文件目录
    private static final String JSON_DIR = "interview_questions";
    // SQL输出目录
    private static final String SQL_OUTPUT_DIR = "generated_sql";
    // 分类映射信息
    private static Map<Integer, String> categoryMap = new HashMap<>();
    
    /**
     * 主方法，执行转换过程
     */
    public static void main(String[] args) {
        try {
            convertJsonToSQL();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 将JSON文件转换为SQL语句
     */
    public static void convertJsonToSQL() throws Exception {
        // 创建输出目录
        Path outputPath = Paths.get(SQL_OUTPUT_DIR);
        if (!Files.exists(outputPath)) {
            Files.createDirectory(outputPath);
        }

        // 获取所有JSON文件
        File jsonDir = new File(JSON_DIR);
        File[] jsonFiles = jsonDir.listFiles((dir, name) -> name.endsWith(".json"));
        
        if (jsonFiles == null || jsonFiles.length == 0) {
            System.out.println("未找到JSON文件");
            return;
        }

        // 先加载分类映射信息
        loadCategoryMap(jsonFiles);
        
        // 创建总的SQL文件
        File allSqlFile = new File(SQL_OUTPUT_DIR + "/all_interview_questions.sql");
        try (BufferedWriter allWriter = new BufferedWriter(new FileWriter(allSqlFile))) {
            // 写入SQL文件头部信息
            allWriter.write("-- 面试问题SQL导入文件\n");
            allWriter.write("-- 自动生成于: " + new Timestamp(System.currentTimeMillis()) + "\n\n");
            
            // 处理每个JSON文件
            for (File jsonFile : jsonFiles) {
                // 跳过包含分类列表的特殊文件
                if (jsonFile.getName().equals("question_list_cid_22.json")) {
                    continue;
                }
                processJsonFile(jsonFile, allWriter);
            }
        }
        
        System.out.println("所有SQL文件已生成，保存在 " + SQL_OUTPUT_DIR + " 目录中");
    }

    /**
     * 加载分类映射信息
     */
    private static void loadCategoryMap(File[] jsonFiles) throws Exception {
        // 查找包含分类列表的文件
        File categoryFile = null;
        for (File file : jsonFiles) {
            if (file.getName().equals("question_list_cid_22.json")) {
                categoryFile = file;
                break;
            }
        }
        
        if (categoryFile != null) {
            ObjectMapper mapper = new ObjectMapper();
            JsonNode rootNode = mapper.readTree(categoryFile);
            JsonNode categoryList = rootNode.path("data").path("qCategoryList");
            
            if (categoryList.isArray()) {
                for (JsonNode categoryNode : categoryList) {
                    int id = categoryNode.path("id").asInt();
                    String name = categoryNode.path("name").asText();
                    categoryMap.put(id, name);
                }
            }
        }
        
        System.out.println("加载了 " + categoryMap.size() + " 个分类映射");
    }

    /**
     * 处理单个JSON文件
     */
    private static void processJsonFile(File jsonFile, BufferedWriter allWriter) throws Exception {
        System.out.println("处理文件: " + jsonFile.getName());
        
        // 从文件名提取分类ID (cid)
        String fileName = jsonFile.getName();
        int categoryId = Integer.parseInt(fileName.replaceAll("question_list_cid_(\\d+)\\.json", "$1"));
        
        // 读取并解析JSON文件
        ObjectMapper mapper = new ObjectMapper();
        JsonNode rootNode = mapper.readTree(jsonFile);
        
        // 获取问题列表
        JsonNode questionList = rootNode.path("data").path("questionList");
        
        if (questionList.isArray()) {
            // 为每个分类创建单独的SQL文件
            String sqlFileName = SQL_OUTPUT_DIR + "/" + fileName.replace(".json", ".sql");
            try (BufferedWriter categoryWriter = new BufferedWriter(new FileWriter(sqlFileName))) {
                // 写入文件头部信息
                categoryWriter.write("-- " + fileName + " 的面试问题SQL导入文件\n");
                categoryWriter.write("-- 分类ID: " + categoryId + "\n");
                categoryWriter.write("-- 问题数量: " + questionList.size() + "\n\n");
                
                // 获取分类名称
                String categoryName = categoryMap.getOrDefault(categoryId, "未知分类");
                
                // 写入分类ID设置语句 (使用实际的分类查找逻辑)
                categoryWriter.write("-- 查找\"" + categoryName + "\"分类的ID\n");
                categoryWriter.write("SET @category_id = (SELECT category_id FROM interview_question_category WHERE category_name = '" + categoryName + "' AND parent_id != 0 LIMIT 1);\n\n");
                
                // 写入插入语句头部
                categoryWriter.write("INSERT INTO interview_question (tenant_id, content, type, difficulty, category_id, answer, analysis, status, del_flag, create_dept, create_by, create_time, update_by, update_time, remark) VALUES\n");
                allWriter.write("-- " + fileName + " 的面试问题\n");
                allWriter.write("SET @category_id = (SELECT category_id FROM interview_question_category WHERE category_name = '" + categoryName + "' AND parent_id != 0 LIMIT 1);\n\n");
                allWriter.write("INSERT INTO interview_question (tenant_id, content, type, difficulty, category_id, answer, analysis, status, del_flag, create_dept, create_by, create_time, update_by, update_time, remark) VALUES\n");
                
                // 处理每个问题
                List<String> valuesList = new ArrayList<>();
                for (JsonNode questionNode : questionList) {
                    String sqlValue = createSqlValue(questionNode);
                    valuesList.add(sqlValue);
                }
                
                // 写入所有值
                for (int i = 0; i < valuesList.size(); i++) {
                    String value = valuesList.get(i);
                    String suffix = (i == valuesList.size() - 1) ? ";\n\n" : ",\n";
                    categoryWriter.write(value + suffix);
                    allWriter.write(value + suffix);
                }
                
                System.out.println("已生成 " + sqlFileName);
            }
        }
    }

    /**
     * 创建SQL值字符串
     */
    private static String createSqlValue(JsonNode questionNode) {
        // 获取问题内容
        String content = escapeSql(questionNode.path("quContent").asText());
        // 获取解析内容
        String analysis = escapeSql(questionNode.path("analysisMd").asText());
        // 默认值
        String tenantId = "000000";
        String type = "简答题";
        String difficulty = "中等";
        String answer = "";
        String status = "0";
        String delFlag = "0";
        String createDept = "103";
        String createBy = "1";
        String updateTime = "NOW()";
        String updateBy = "1";
        String remark = "NULL";
        
        // 构建SQL值字符串
        return String.format("('%s', '%s', '%s', '%s', @category_id, '%s', '%s', '%s', '%s', %s, %s, NOW(), %s, %s, %s)", 
                tenantId, content, type, difficulty, answer, analysis, status, delFlag, 
                createDept, createBy, updateBy, updateTime, remark);
    }

    /**
     * 转义SQL字符串中的特殊字符
     * 与原始SQL文件保持一致，使用反斜杠转义单引号
     */
    private static String escapeSql(String str) {
        if (str == null) {
            return "";
        }
        // 按照原始SQL文件的转义方式处理特殊字符
        // 注意：必须先处理反斜杠，再处理单引号
        return str.replace("\\", "\\\\")
                 .replace("'", "\\'")
                 .replace("\r\n", "\\n")
                 .replace("\n", "\\n")
                 .replace("\r", "\\n")
                 .replace("\t", "\\t")
                 .replace("\"", "\\\"")
                 .trim();
    }
}